from utils.database_table import FusedRoadTable, ReportRoadTable, RSNodeTable, RemoteSensingRoadTable, Similarity
from utils.basic_function import *
from prepocessing.snap_nodes import FuseSnapTool
from prepocessing.remove_pseudo_nodes import RemovePseudoNodesTool
from prepocessing.manual import ManualTool
import geopandas as geopd


def merge_fuse_tables(prefix, merged_table_name):
    # 将所有道路合并到同一个图层
    merged_table = FusedRoadTable(merged_table_name)
    merged_table.create_table(drop=True)

    with conn:
        with conn.cursor() as cursor:
            for r_class in R_CLASS_LIST[:-1]:
                fuse_table = ReportRoadTable(f"{prefix}_{r_class.lower()}")
                use_cols = list(fuse_table.dtype_dict.keys())
                use_cols.remove(fuse_table.pk)
                sql = f"insert into {merged_table.name} ({','.join(use_cols)}) select {','.join(use_cols)} from {prefix}_{r_class.lower()}"
                cursor.execute(sql)

            sql = f"""insert into {merged_table_name} ({merged_table.segment_code}, {merged_table.road_code}, {merged_table.start_stake},
                {merged_table.end_stake}, {merged_table.geom})
                select 'N','N',0,st_length({RemoteSensingRoadTable.geom})/1000,{RemoteSensingRoadTable.geom} from {DataIO.MATCH_OUTPUT_RS_PREFIX}_n"""
            cursor.execute(sql)

    merged_table.update_length()
    merged_table.add_primary_key()
    merged_table.create_index(merged_table.geom, True)


def fuse_postprocessing(fuse_prefix, output_prefix):
    # 融合结果备份
    for r_class in R_CLASS_LIST[:-1]:
        FusedRoadTable(f"{fuse_prefix}_{r_class}".lower()).create_backup("backup")

    new_road_table = RemoteSensingRoadTable("match_lx_n")
    new_road_table.restore_table_from("match_lx_n_backup")
    rt = RemovePseudoNodesTool(new_road_table, CLUSTER_TOLERANCE_R)
    rt.remove_pseudo_nodes_table()

    fuse_table = FusedRoadTable("fuse_all")
    # 合并已有路段、新增路段到同一个表
    merge_fuse_tables(fuse_prefix, fuse_table.name)
    fuse_table.create_backup("backup")

    # 化简geom
    # fuse_table.restore_table_from("fuse_all_backup")
    fuse_table.simplify(0.1)

    # 端点捕捉
    st = FuseSnapTool(fuse_table, CLUSTER_TOLERANCE_R, 5, 5, True, 50)
    st.snap_nodes_table()
    st = FuseSnapTool(fuse_table, CLUSTER_TOLERANCE_R, 20, 5, True, 50)
    st.snap_nodes_table()
    fuse_table.create_backup("snap")

    mt = ManualTool(fuse_table)
    mt.find_all()  # todo 暂时没处理

    # 拆分
    fuse_table.restore_table_from(f"{fuse_table.name}_snap")
    for r_class in R_CLASS_LIST:
        fuse_table_i = FusedRoadTable(f"{output_prefix}_{r_class}".lower())
        fuse_table.move_rows(fuse_table_i, where=f"where substring({fuse_table.segment_code},1,1)='{r_class}'")
        fuse_table_i.add_primary_key()
        fuse_table_i.create_index(fuse_table_i.geom, is_geom=True)
